All Categories :
Servers
Chapter 16
Linking Databases to the Web
CONTENTS
As surely as the Intranet is changing the face of client/server
application development, you can bet that organizations all over
the world are striving to build Web databases. Nearly every client/server
application you can imagine is based on a back-end database located
on a server. And nearly everywhere you look, there is a backlog
of requests for application databases that need to be built and
put into service to help automate office processes.
One reason that Web technology is getting so much attention on
the Intranet is that it has the potential to help alleviate the
application development backlog by standardizing and simplifying
the database front end. (I'm referring to our hero, the Web browser,
of course. ) Conveniently, the back end is simplified too. Using
ODBC (Open Database Connectivity), IIS, and some simple HTML scripts,
you can provide access to any legacy database system. When compared
with the traditional approach of custom programming in third-generation
languages, Web databases may substantially reduce implementation
and maintenance costs.
As with so many other computer applications' vendors, commercial
database vendors are racing to provide Web-accessible front ends
to their packages. If you've struggled to build useful, user-friendly
database applications using the tools your database vendor has
provided or using custom programming, the idea of using a Web
browser and fill-in forms as an alternative to building user interfaces
from scratch is an attractive option.
In this chapter, you'll learn about a few commercial database
vendors' Web products. I'll explain ODBC and the raw fundamentals
of SQL programming. I'll also show you how to create user-friendly
custom database applications using only IIS and HTML with no additional
programming.
This chapter does not substantially cover designing and developing
relational database applications. Consult your database package's
documentation for that information. This chapter assumes that
you are thinking of a database application you would like to access
using your Web browser. Of course, you may find once you start
accessing your database with your Web browser, you'll want to
change the database's design. This is no different from the traditional
iterative database application development process, in which you
and the users try the application for awhile to see how you want
to change it.
This chapter is mostly about the subject of creating full-blown
client/server database applications on the Web. But before diving
too deeply into that subject, this section reviews the ideas presented
in Part III, "Setting Up Office Applications." Chapters 12
through 15 showed how easy it is to set
up any type of document, including a database, for Web browser
access using MIME. In some cases, this simple concept may be all
that you need to accomplish the task at hand.
In Chapter 15,"Other Client Applications
on the Intranet," you learned how to access Microsoft Access
database applications through the Web helper application mechanism.
You can configure your Web server to serve complete Access databases,
just as it serves any other file on the server. Your customers'
Web browsers can then use their own copies of Access as a Web
browser helper application to load the databases for data search
and retrieval and/or export to other applications.
Note that such helper application access to a database application
should be considered read-only because the Web browser downloads
a temporary copy of the database to the local system. All queries
made by your customer are based on the temporary copy, and any
changes that he might attempt to make will not be reflected in
the master copy on your Web server. Similarly, updates made to
the master server won't be propagated to any client unless the
client reloads the database from the Web server. Thus, you'll
want to limit the capabilities of such an arrangement to running
queries, generating reports, and exporting data from the application.
This limitation is more than offset by other capabilities, however.
For example, if your company uses other programs, such as Microsoft
Office or its individual components, you'll be able to use their
capabilities to move data from one application to another. You
can import information in Access databases, for example, into
Word or Excel (and vice versa).
Whatever database package you use on your Intranet-and whatever
bells and whistles it provides for developing database applications,
entering queries, and generating reports-database access boils
down to two broad processes:
- Formulating and submitting structured query language (SQL)
queries or data-entry statements to the database engine.
- Receiving and processing the results of the query.
Whether the user hand-edits SQL queries or fills in an on-screen
query or data-entry form, the objective is the same: to pass the
query or new data to the database back end. Similarly, when the
database spits out the results of a query or data entry, an application
has to receive it and generate human-readable output (on-screen
or on paper) or machine-readable output in some specific format.
Web access to these databases involves the same processes, with
important differences:
- Your customers perform queries and data entry using fill-in
Web forms (created with HTML) in which they enter query keywords
or other search criteria through menu selections, click buttons,
free-form text blocks, or fill-in-the-blank boxes.
- CGI or ISAPI applications take the information entered in
the form and bundle it up into valid SQL queries or data-entry
updates. They then pass it off to the database back end.
- The same CGI or ISAPI applications receive the results back
from the database engine after processing. They format the report
in HTML and pass it back to the customer's Web browser for display.
This section briefly lists a few of the commercial Web database
products available for Windows NT. If you are faced with a new
client/server database project, and you don't want to engineer
it yourself, any of these Web tools will help. You will still
have to determine the database model and the user interface (using
HTML). You can find out more about these Web database products,
and in some cases even download a trial version, by visiting these
URLs:
FoxWeb for FoxPro
FoxPro users should look at a new product from the Aegis Group
called FoxWeb. This software tool interfaces Windows Web servers
with FoxPro data and programs. FoxWeb overcomes the limitations
of other Windows CGI approaches, which read and write temporary
files to pass environment variables between processes. It works
by running multiple, background Visual FoxPro instances simultaneously,
each one of which can handle CGI interactions. CGI environment
variables are placed into FoxPro arrays and objects for manipulation.
All programming is done in FoxPro rather than in an external scripting
language like Perl, so your investment in FoxPro programming can
be both preserved and leveraged. You can even store reusable HTML
code directly in FoxPro databases for easy retrieval, with intelligent
branching capabilities.
Aegis claims substantially faster database access compared to
ODBC database transactions, although ODBC database applications
can also access FoxPro databases, as noted later in the section
on ODBC. FoxWeb requires version 3.0 of Visual FoxPro. FoxWeb
includes login/password security features.
WebBase
ExperTelligence, Inc. offers WebBase for all Microsoft Windows
platforms. This package is a 32-bit HTTP server with built-in
hooks for accessing databases without the use of CGI scripting.
As a Web server, WebBase can serve conventional HTML documents
in response to Web browser requests. Besides this function, however,
the package supports embedded SQL code in special HTML documents,
which, when accessed, can contact database applications directly
to run queries or data-entry commands. WebBase HTML extensions
also include a macro language featuring intelligent decision-making
constructs like if-then and
case branching, as well as
forRow and forIndex
looping. A number of other useful functions are also provided,
such as string-comparison/matching, math, date handling, and other
logic. These features enable customized responses to Web browser
requests based on user name, IP address, browser type, and the
like.
WebBase enables the session state to be maintained throughout
a user's session and has login/password security built in. Any
ODBC database (see the next section for a discussion of ODBC)
is supported (for example, Microsoft Access, Excel, SQL Server,
FoxPro, dBASE III and IV, Paradox, Btrieve, as well as UNIX database
servers running Sybase and Oracle).
The package can also search fielded text files as a database.
Although WebBase can function as a Web server, you can also run
a traditional Web server for better HTTP performance, either on
the same computer or a different one, because WebBase doesn't
provide all the functions of full-featured Web servers. WebBase
runs on all Intel Windows platforms.
All the previously mentioned database products support ODBC. Microsoft
Open Database Connectivity (ODBC) is a standard programming interface
for application developers and database systems providers. Before
ODBC became a de facto standard for Windows programs to interface
with database systems, programmers had to use proprietary languages
for each database they wanted to connect to. Now ODBC has made
the choice of the database system almost irrelevant from a coding
perspective, which is as it should be. Application developers
have much more important things to worry about than the syntax
that is needed to port their program from one database to another
when business needs suddenly change.
Through the ODBC Administrator in Control Panel, you can specify
the particular database that is associated with a data source
that an ODBC application program is written to use. Think of an
ODBC data source as a door with a name on it. Each door will lead
you to a particular database. For example, the data source named
Sales Figures might be a SQL Server database, whereas the Accounts
Payable data source could refer to an Access database. The physical
database referred to by a data source can reside anywhere on the
LAN.
Note |
The ODBC system files are not installed on your system by Windows NT. Rather they are installed when you set up a separate database application, such as SQL Server Client or Visual Basic. When the ODBC icon is installed in Control Panel, the icon uses a file called ODBCCP32.CPL. You also can administer your ODBC data sources through a stand-alone program called ODBCADM.EXE (16-bit) or ODBCAD32.EXE (32-bit). The 16-bit and 32-bit versions maintain separate lists of ODBC data sources. The 16-bit data sources can be used only by 16-bit programs. The same goes for 32-bit data sources and 32-bit programs (unless the programmer uses an advanced technique known as thunking). On Windows NT, there is a further classification of data sources called system data sources. These sources can be used only by 32-bit NT services, such as IIS.
|
From a programming perspective, the beauty of ODBC is that the
application can be written to use the same set of function calls
to interface with any data source, regardless of the database
vendor. The source code of the application doesn't change, regardless
of whether it talks to Oracle or SQL Server. (I only mention these
two as an example. ODBC drivers are available for several dozen
popular database systems. Even Excel spreadsheets and plain text
files can be turned into data sources.)
The operating system uses the Registry information written by
ODBC Administrator to determine which low-level ODBC drivers are
needed to talk to the data source (such as the interface to Oracle
or SQL Server). The loading of the ODBC drivers is transparent
to the ODBC application program. In a client/server environment,
the ODBC API even handles many of the network issues for the application
programmer.
The advantages of this scheme are so numerous that you are probably
thinking there must be some kind of a catch. The only disadvantage
of ODBC is that it isn't as efficient as talking directly to the
native database interface (although Microsoft is planning to make
ODBC the native interface of SQL Server). ODBC has had many detractors
make the charge that it is too slow. Microsoft has always claimed
that the critical factor in ODBC performance is the quality of
the driver software that is used. In my humble opinion, this claim
is valid. The availability of good ODBC drivers has improved a
great deal recently.
The criticism about ODBC performance is somewhat analogous to
those who said that compilers would never match the speed of pure
assembly language. Of course not! But the compiler (or ODBC) gives
you the opportunity to write cleaner programs, which means you
finish sooner and are ready to enhance your program or optimize
the section of code where it truly spends most of its execution
time. Meanwhile, computer hardware gets faster every year. (As
a parody of the personal career tragedy that can occur due to
the ever-declining percentage of application programs being developed
without any direct use of assembly language, the Microsoft Day
at the Movies in April 1996 included a humorous skit showing two
jobless, die-hard programmers who wanted to reinvent the Win32
common dialogs in assembly code. As I recall, they couldn't see
why anyone would prefer to write one line of "slow"
compiled code when pages and pages of ultra-fast assembly code
could be written instead.)
The basics of CGI in this section are relevant to Web database
application interfaces, and you'll want to bear them in mind as
you work your way through the rest of this chapter:
- Each piece of data your customer enters into an HTML fill-in
form (query keywords or new data entry) is available to be passed,
as standard input, directly to your database engine by your CGI
or ISAPI program.
- You can include additional data in form output using the INPUT
TYPE=hidden HTML markup. You can hard-code this information
into your forms or you can dynamically set it based on user behavior
or other factors that the customer doesn't see, but that your
CGI program might need for processing.
- CGI and ISAPI programs have access to a good deal of standard
information in the form of environment variables. The variables
include not only the customer's Web browser type, but also the
TCP/IP address and hostname of the user's computer, his userid
and access authentication (if the server is configured to provide
it), and the MIME data type/subtypes supported by the browser.
Writing a CGI application that would interact with an off-the-shelf
database would take quite a bit of programming expertise. Why
take the time to write such an application or pay someone else
to do it when perfectly good ones already exist? I have included
on the CD-ROM a trial version of a product called Cold Fusion,
which is a CGI application that allows full Web integration with
any 32-bit ODBC database application. The trial version is a completely
operational version; it's only limitation is that it will stop
working 30 days after installation. At which time you are encouraged
to buy the product if you find it useful.
You can use Cold Fusion to create a wide range of Intranet applications
including company schedules, customer feedback, online order entry,
event registration, searching of catalogs, directories and calendars,
bulletin-board style conferencing, online technical support, and
interactive training. If you have browsed the World Wide Web and
come across any Windows NT servers, you probably have seen the
logo saying, "Powered by Cold Fusion"; it is a popular
product. Cold Fusion is sold by Allaire at http://www.allaire.com/.
You create Cold Fusion applications by combining standard HTML
files with high-level database commands and a powerful CGI program
that is precompiled. This method of developing Web applications
is an order of magnitude faster, more robust, and more flexible
than first generation, code-intensive techniques.
Cold Fusion applications can be developed very rapidly because
no code (beyond simple HTML) is required. The applications are
also robust because all database interactions are encapsulated
in a single industrial-strength CGI script.
Cold Fusion applications are also very flexible because all formatting
and presentation is done using standard HTML files that can be
modified and revised at any time (as opposed to having to edit
and recompile source code).
Installing Cold Fusion
The installation of the Cold Fusion demo is a snap. Just copy
the file cfafeval.exe from
the CD to a temporary directory on your hard disk. Then execute
the self-extracting install program in your temporary directory.
The Cold Fusion installation process will handle the rest.
Note |
The file cfafeval.exe on the CD-ROM actually includes two products: Cold Fusion 1.5 and Allaire Forums. Allaire Forums is a Web collaboration product mentioned in Chapter 27, "Collaboration on Your Intranet." These evaluation products are provided through the courtesy of J.J. Allaire.
|
The Cold Fusion demo application includes a complete online tutorial
in HTML format that guides you through the development of several
examples. You may want to spend some time with the Cold Fusion
online tutorial and examples.
Note |
If the Cold Fusion installation of ODBC components does not complete successfully, an ODBC library was probably in use by Windows or another application during setup. In this case, restart Windows and double-click the ODBC Setup icon in the Cold Fusion Program Group to complete the installation of ODBC. To avoid this problem, you should exit all running application programs before you install new software.
|
The HTML Link to Cold Fusion
Suppose a fictitious ABC Corporation has a company mailing list
and it's in the Microsoft Access *.mdb
file format. Let's say that the new V.P. has decided that employees
should be able to add their names to the mailing list to volunteer
their free time to promote company products. With this stalwart
example in mind, you will no doubt be able to think of ways you
can adapt this application for your own purposes. Using Cold Fusion,
you can create a form that people can fill out and have the data
automatically entered into your Access MDB file. A description
of the files for this project is as follows:
- mlist.htm contains the
form for entering information to the database. See Figure 16.1
for the mailing list HTML page displayed in Internet Explorer
3.0. Please see Listing 16.1 for the HTML source code that provides
an example of how to use Cold Fusion.
- mlist.mdb is the Access
database that has a table named MLIST with columns matching the
fields on the HTML form.
Figure 16.1: The ABC Intranet Mailing List page for propaganda distribution.
Listing 16.1. The mlist.htm
file uses Cold Fusion as a CGI database application.
<HTML>
<HEAD>
<TITLE>Intranet Mailing List</TITLE>
</HEAD>
<BODY>
<H1>The ABC Corporation Intranet Mailing List</H1>
<HR>
<P>
Occasionally, ABC will send out snail mail containing brochures of
our new products. If you would like to get on the list and volunteer
to distribute these materials to your neighbors, just fill out the
form below.
<HR>
<FORM ACTION="/scripts/dbml.exe?Action=insert" METHOD="POST">
<!-- Cold Fusion configuration fields -->
<INPUT TYPE="hidden" NAME="DataSource" VALUE="Mailing List">
<INPUT TYPE="hidden" NAME="TableName" VALUE="MLIST">
<INPUT TYPE="hidden" NAME="NextPage" VALUE="/mlist/thanks.htm">
<!-- Data entry fields -->
<PRE>
First Name:<INPUT NAME="FirstName" size=28>
Last Name:<INPUT NAME="LastName" size=28>
Address:<INPUT NAME="Address" size=45>
City:<INPUT NAME="City" size=15>
State:<INPUT NAME="State" size=5>
Zip:<input NAME="PostalCode" size=10>
E-mail:<INPUT NAME="EmailAddress" size=35>
Phone:<INPUT NAME="Phone" size=35>
Extension:<INPUT NAME="Extension" size=7>
<INPUT Type="submit" Value=" When Done, Click Here! ">
<INPUT Type="reset" Value="Clear Entries">
</PRE>
</BODY>
</HTML>
Gaining CGI Database Features without Programming
To add record insert functionality to the database at your sample
site, follow these steps:
- Create a directory named mlist
underneath your HTML document root directory.
D:\iis\wwwroot\mlist\
is an example.
- Copy the files mlist.htm
and mlist.mdb to the mlist
directory. Since the database will be modified by the Cold Fusion
CGI application, you need to ensure that the database file is
located in a directory in which the IUSR_computername
account has write permission. (The database is not required to
be in the same directory as the HTML file.)
- Modify your HTML home page to provide a link to mlist.htm.
- Run the Cold Fusion Administrator (fusionad.exe)
and create one or more ODBC data sources for MS Access, as described
in the following steps. For more information on what ODBC is and
how it works, see the Cold Fusion User's Guide.
- Choose the Add button to display the Add Data Source dialog
shown in Figure 16.2. Select the driver for the Microsoft Access
driver, and then choose OK.
Figure 16.2: Adding a data source.
- In the dialog box that appears, enter Mailing
List for the Data Source Name. See Figure 16.3.
Figure 16.3: Naming a data source.
- Choose the Select button to enter the path to the mlist.mdb
file from Step 2. Choose OK to close the Select Database dialog.
Choose OK again to close the ODBC Microsoft Access Setup dialog.
Your Data Sources tab should appear similar to Figure 16.4.
Figure 16.4: The result of adding the Mailing List DSN to Cold Fusion Administrator.
- Before closing Cold Fusion Administrator, select the Mailing
List DSN in the Data Sources tab and click on the Verify button.
Cold Fusion will test the DSN and the location of the physical
database file.
Your site now has a link to a page that will automatically add
people to your mlist.mdb
file containing your mailing list. Now is a good time to break
out your Web browser and add a few names to the list. Of course,
you will need to use Microsoft Access, or a similar Access database
program, to view, modify, or print out any information from the
.mdb file. The next step
is to edit and search the database from the Web.
Inserting and Updating Data with Cold Fusion
Cold Fusion's most basic functionality (inserting form data into
database tables) is implemented by the addition of hidden configuration
fields to HTML forms. If you are already familiar with HTML forms,
this technique is extremely easy to learn. If you need a refresher
course on HTML forms, please refer to Chapter 5,
"What You Need to Know About HTML."
To insert or update data, you create an HTML form containing the
fields in the database table that you want to insert or update.
You then add three hidden fields to the form that indicate what
you want Cold Fusion to do with the data entered by the user.
These hidden fields are as follows:
Field Name | Purpose
|
DataSource
| Name of the ODBC data source containing your table
|
TableName
| Name of the table you want the form fields written to
|
NextPage
| A URL indicating which page the user should be sent to if the submission is successful
|
Additional hidden fields can be specified to validate the user's
entries (for example, required, numeric, date, and range-checked).
When this form is submitted to the Cold Fusion CGI program (DBML.EXE),
the data entered by the user is added to the specified table and
the user is routed to the specified next page.
Queries and Dynamic Pages with Cold Fusion
Cold Fusion enables you to dynamically generate HTML pages based
on user queries. These queries are submitted to the Cold Fusion
CGI program (DBML.EXE), which
then (based on a template file specified in the query) generates
the output to be sent back to the user.
The key to dynamic page generation is a small (but powerful) set
of database-oriented markup tags. These tags are collectively
referred to as DBML (Database Markup Language). DBML tags are
very similar to HTML tags except they are database-oriented. Learning
to use the DBML tags is extremely simple. Almost all of the core
functionality of Cold Fusion is encapsulated in these four tags:
Tag | Purpose
|
DBQUERY
| Submits an SQL query to the database |
DBOUTPUT
| Displays the result of a query, freely intermixing result set fields and HTML tags
|
DBTABLE and DBCOL
| Displays a preformatted table containing the result set of a query
|
Dynamic pages are created using template files, which are composed
of a mix of HTML and DBML tags that define how the user's request
should be processed and what type of output should be returned.
The DBML tags are used to specify how you want Cold Fusion to
interact with the database, as well as where you want to display
the results of your queries. For example, you might specify that
you want the SQL query SELECT * FROM
Customers sent to the database and the results returned
as a preformatted table.
Tip |
For more information about the fundamentals of Structured Query Language (SQL), see the following section titled "Overview of Structured Query Language."
|
The HTML tags are used both for implementing the nondatabase-driven
parts of your output (for example, page header and footer) as
well as for specifying how you want the results of your queries
formatted. For example, you might specify that you want a field
bolded or a horizontal rule drawn between each displayed record.
Depending on the nature of your data, you might decide that you
would not want a link like this available to the general public.
If this is the case, you'll want to place password protection
on your HTML pages. Most Web servers, including IIS, enable you
to password-protect your pages. (See Chapter 10,
"Intranet Security in Windows NT," for more information
about Intranet security.)
Most database tasks can be accomplished with just four simple
SQL statements:
- SELECT returns
a list of matching records.
- DELETE deletes
matching records.
- UPDATE modifies selected
fields in matching records.
- INSERT adds a new row
of data; values must be supplied for the required fields.
What follows are several short and sweet examples of each type
of statement. In each case, it is not important to type in uppercase,
but I have marked the SQL keywords in uppercase for readability.
I use the terms table1,
field1, and field2
to represent placeholders for the object names that you would
use in your database.
The following are some examples of the SELECT
statement:
- SELECT * FROM table1
- This statement will return all fields from all rows of the
given table.
- SELECT field1, field2 FROM table1
- This statement selects all rows but only returns two fields.
It is more efficient than using the * wildcard in client/server
database systems because only the data in the fields you need
will be copied across the network.
- SELECT * FROM table1 WHERE field1 = 'abc*' AND
field2 > 99
- The WHERE clause is added
here to select particular rows. This example assumes that field1
is a text field. The asterisk is used as a wildcard to match any
data that begins with 'abc'.
Single quotes are needed for text constants. The AND
keyword is used to also select rows in which field2
(numeric) is greater than 99.
Note |
The asterisk wildcard character is specific to the Access dialect of SQL. Standard SQL uses the percent (%) character instead. Another special character that differs among SQL dialects is the date delimiter. Access SQL surrounds dates with pound signs (#), while ODBC merely requires the date to be in one of several formats (with no special delimiters).
|
Now that you know several varieties of SELECT,
the DELETE statement is easy.
The following is an example:
DELETE FROM table1 WHERE field1 = 24
This statement will delete all rows from the selected table that
match the optional WHERE
clause.
The UPDATE statement is a
little trickier. It can be used to modify one or more rows of
a selected table:
UPDATE table1 SET field1 = 99 WHERE field1 = 44
This statement will change the data in the field1
column for all rows that currently have a field1
value of 44. Other field=value pairs may be included as long as
they are separated by commas.
The INSERT statement is also
a little different from the others. Whereas the other statements
operate on existing data, INSERT
is used to add a new record:
INSERT INTO table1 (field1, field2) VALUES (99, 'abc')
Assuming that field1
and field2 are the
only required fields, this statement creates a new row in the
selected table. The order of the values must match the order of
the selected column names. For example, 99 will be placed in field1,
and 'abc' will be placed
in field2.
This chapter focused on Web interfaces to relational database
packages. In addition, SQL and ODBC were discussed, because they
form the pillars of most database projects. I talked about several
vendors (but it was by no means an exhaustive list) of commercial
database products for Windows NT and the Web. The chapter surveyed
a fairly representative sample, some of them in detail, to at
least give you a firm idea of what's possible in this rapidly
growing field.
Chapter 17, "Understanding ActiveX
Technologies," continues to explore the advanced Internet
technologies that Microsoft is building on top of IIS. Although
this chapter dealt with the creation of Web-accessible, general-purpose
databases, you may find Chapter 21, "Indexing
Your Intranet with WAIS," to be of similar interest as it
looks at the subject of searching your Web.

Contact
reference@developer.com with questions or comments.
Copyright 1998
EarthWeb Inc., All rights reserved.
PLEASE READ THE ACCEPTABLE USAGE STATEMENT.
Copyright 1998 Macmillan Computer Publishing. All rights reserved.